package com.lp.mgt.utils;

import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Comparators;
import com.lp.mgt.entities.TableInfo;

import lombok.extern.slf4j.Slf4j;
@Slf4j
public class ExcelUtil {
	//导出：response,表头数组，数据
	public static <T> void exportExcel(HttpServletResponse response,List<TableInfo> table,List<T> data,String excelName) {
		//提取表备注做excel表头。起始就是导出没数据的格式
		List<String> header=table.stream()
				.sorted((a1,a2) -> Integer.parseInt(a1.getOrdinalPosition())-Integer.parseInt(a2.getOrdinalPosition()))
				.map(TableInfo::getColumnComment)
				.collect(Collectors.toList());
		String[] headers=header.toArray(new String[header.size()]);//备注字段数组
		//获取列名列表用于和实体类比较
		List<String> columns=table.stream()
				.sorted((a1,a2) -> Integer.parseInt(a1.getOrdinalPosition())-Integer.parseInt(a2.getOrdinalPosition()))
				.map(TableInfo::getColumnName)
				.collect(Collectors.toList());
		//创建HSSFWOOKBOOK对象
		HSSFWorkbook workbook = new HSSFWorkbook();
		//创建一个sheet页
		HSSFSheet sheet=workbook.createSheet("第一页");
		//设置excel表头
		setTitle(workbook,sheet,headers);
		//设置单元格并赋值
		if(data!=null&&data.size()>0) setData(workbook,sheet,columns,data);
        //设置浏览器下载
        setBrowser(response, workbook,excelName.indexOf(".xls")!=-1?excelName:excelName+".xls");
	}
	

	//表头
	private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] headers) {
		try {
		//创建一行
		HSSFRow row=sheet.createRow(0);
		//设置列宽
		for(int i=0;i<=headers.length;i++) {
			sheet.setColumnWidth(i, 15*256);
		}
		//设置为居中加粗,格式化时间格式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);//居中
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        font.setColor(Font.COLOR_RED);
        style.setFont(font);//加粗
        HSSFCell cell;
        for (int j = 0; j < headers.length; j++) {
            cell = row.createCell(j);//创建单元格
            cell.setCellValue(headers[j]);//单元格赋值
            cell.setCellStyle(style);//单元格样式
        }
		}catch (Exception e) {
			log.debug("表头设置失败");
			e.getMessage();
		}

	}
	private static <T> void setData(HSSFWorkbook workbook,HSSFSheet sheet,List<String> columns,List<T> data) {
		for(int i=0;i<data.size();i++) {//共多少行数据
			HSSFRow row=sheet.createRow(i+1);//创建一行,0行为表头所以从第一行开始写数据
			//获取数据对象
			T t=data.get(i);
			Field[] fields=t.getClass().getDeclaredFields();//反射获取数据对象所有属性
			for(int j=0;j<columns.size();j++) {//依次遍历所有列。列是有序的
				for(Field fd:fields) {//便利所有属性
					if(columns.get(j).equalsIgnoreCase(fd.getName())) {
						fd.setAccessible(true);
				        HSSFCellStyle style = workbook.createCellStyle();
				        HSSFFont font = workbook.createFont();
				        HSSFDataFormat format=workbook.createDataFormat();
				        font.setBold(false);//居中
				        style.setFont(font);//加
				        style.setDataFormat(format.getFormat("yyyy年mm月dd日"));
						//创建一个单元格
						HSSFCell cell=row.createCell(j);//同时可以设置样式
						cell.setCellStyle(style);//设置样式
						//判断值类型
						try {
							System.out.println(fd.getType().getTypeName()+"\n");
							switch (fd.getType().getTypeName()) {
							case "java.util.Date":
								cell.setCellValue((Date)fd.get(t));
								break;
							default:
								cell.setCellValue(fd.get(t)==null?"":fd.get(t).toString());
								break;
							}
						} catch (IllegalArgumentException e) {
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							e.printStackTrace();
						}
						break;//如果赋值了就停止本次循环
					}
				}
			}
		}
	}
	
	

	
	private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String excelName) {
		try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName,"UTF-8"));
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("文件导出成功！");
        } catch (Exception e) {
            log.info("文件导出失败！");
            e.printStackTrace();
        }
	}

	//导入。先上传/再解析数据/之后保存
	public static <T> List<T> importExcel(List<TableInfo> table,MultipartFile file,T t) {
	        log.info("导入解析开始，fileName:{}",file);
			//获取列名列表用于和实体类比较
			List<String> columns=table.stream()
					.sorted((a1,a2) -> Integer.parseInt(a1.getOrdinalPosition())-Integer.parseInt(a2.getOrdinalPosition()))
					.map(TableInfo::getColumnName)
					.collect(Collectors.toList());
	        try {
	            List<T> list = new ArrayList<>();
	            InputStream inputStream = file.getInputStream();
	            Workbook workbook = new HSSFWorkbook(inputStream);
	            Sheet sheet = workbook.getSheetAt(0);//获得的一个sheet页
	            //获取sheet的行数
	            int rowsNum = sheet.getPhysicalNumberOfRows();
	            for (int i = 1; i < rowsNum; i++) {//从第一行开始。第0行表头
	            	Field[] fields=t.getClass().getDeclaredFields();
	                //获取当前行的数据
	                Row row = sheet.getRow(i);
	                int cellsNum=row.getPhysicalNumberOfCells();
	                for(int j=0;j<cellsNum;j++) {//这个顺序可对应表信息字段顺序，从而给对象字段赋值
	                	Cell cell=row.getCell(j);//依次获取单元格
	                	for(Field field:fields) {
	                		field.setAccessible(true);
	                		if(columns.get(j).equalsIgnoreCase(field.getName())) {
			                    if (cell.getCellType().equals(CellType.NUMERIC)) {
			                        field.setInt(t,(int) cell.getNumericCellValue());
			                    }
			                    if (cell.getCellType().equals(CellType.STRING)) {
			                        field.set(t, cell.getStringCellValue());
			                    }
			                    if (cell.getCellType().equals(CellType.BOOLEAN)) {
			                        field.setBoolean(t,cell.getBooleanCellValue());
			                    }
			                    if (cell.getCellType().equals(CellType.ERROR)) {
			                        field.set(t, cell.getErrorCellValue());
			                    }
	                		}
	                	}
	                }
	                list.add(t);
	            }
	            System.out.println(list);
	            log.info("导入文件解析成功！");
	            return list;
	        }catch (Exception e){
	            log.info("导入文件解析失败！");
	            e.printStackTrace();
	        }
	        return null;
	    }

}
